package vn.edu.saigontech.ncms.web.student;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;

import vn.edu.saigontech.ncms.model.enumeration.Gender;
import vn.edu.saigontech.ncms.model.info.AdditionalInfo;
import vn.edu.saigontech.ncms.model.info.Student;
import vn.edu.saigontech.ncms.model.info.SurveyInfo;
import vn.edu.saigontech.ncms.model.program.Level;
import vn.edu.saigontech.ncms.service.LevelService;
import vn.edu.saigontech.ncms.service.PlacementShiftService;
import vn.edu.saigontech.ncms.service.ProgramService;
import vn.edu.saigontech.ncms.service.ScholarshipService;
import vn.edu.saigontech.ncms.service.SchoolGradeService;
import vn.edu.saigontech.ncms.service.SchoolNameService;
import vn.edu.saigontech.ncms.service.StudentService;
import vn.edu.saigontech.ncms.service.StudentTypeService;
import vn.edu.saigontech.ncms.service.SurveyInfoService;
import vn.edu.saigontech.ncms.service.UserService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.vo.SelectOption;
import vn.edu.saigontech.ncms.web.BaseAction;
import flexjson.JSONSerializer;

@SuppressWarnings("serial")
public class StudentAction extends BaseAction {
	
	private Long id;
	private Long[] ids;
	private String firstName;
	private String lastName;
	private String nickName;
	private String gender;
	private String dateOfBirth;
	private String address;
	private String phoneNumber;
	private String email;
	private String comment;
	private Long scholarshipId;
	private Long placementShiftId;
	private Long typeId;
	
	private String consultedDate;
	private String fromConsultedDate;
	private String toConsultedDate;
	private Long programId;
	private String testDate;
	private Long consultantId;
	
	/* halnt */
	private String school;
	private String clazz;
	private String studiedEnglishCenter;
	private Long schoolGradeId;
	
	private Long[] surveyInfos;

	public String showAdd() {
		REQUEST().setAttribute("types", StudentTypeService.INSTANCE.findAll());
		REQUEST().setAttribute("scholarships", ScholarshipService.INSTANCE.findAll());
		REQUEST().setAttribute("shifts", PlacementShiftService.INSTANCE.findAll());
		REQUEST().setAttribute("programs", ProgramService.INSTANCE.findAllAreActive());
		REQUEST().setAttribute("consultants", UserService.INSTANCE.findByRole("Consulting"));
		REQUEST().setAttribute("schoolGrades", SchoolGradeService.INSTANCE.findAll());
		REQUEST().setAttribute("schoolNames", SchoolNameService.INSTANCE.findAll());
		REQUEST().setAttribute("surveyInfos", SurveyInfoService.INSTANCE.findAllAreActive());
		return SUCCESS;
	}

	public String add() {
		Student student = new Student();
		student.setFirstName(this.firstName);
		student.setLastName(this.lastName);
		student.setNickName(this.nickName);
		student.setGender(Gender.create(this.gender));
		student.setDateOfBirth(DateUtil.fromString(this.dateOfBirth, this.i18n("format.date")));
		student.setAddress(this.address);
		student.setPhoneNumber(this.phoneNumber);
		student.setEmail(this.email);
		student.setScholarship(ScholarshipService.INSTANCE.findById(this.scholarshipId));
		student.setPlacementShift(PlacementShiftService.INSTANCE.findById(this.placementShiftId));
		student.setType(StudentTypeService.INSTANCE.findById(this.typeId));
		student.setComment(this.comment);
		student.setConsultedDate(DateUtil.fromString(this.consultedDate, this.i18n("format.date")));
		student.setProgram(ProgramService.INSTANCE.findById(this.programId));
		student.setTestDate(DateUtil.fromString(this.testDate, this.i18n("format.date")));
		student.setConsultant(UserService.INSTANCE.findById(this.consultantId));
		
		if(!Checker.isNull(this.surveyInfos)) {
			Set<SurveyInfo> infos = new HashSet<SurveyInfo>(); 
			for (Long each : this.surveyInfos) {
				SurveyInfo surveyInfo = SurveyInfoService.INSTANCE.findById(each);
				infos.add(surveyInfo);
			}
			student.setSurveys(infos);
		}
		
		/* halnt */
		AdditionalInfo additionalInfo = new AdditionalInfo();
		additionalInfo.setSchool(this.school);
		additionalInfo.setClazz(this.clazz);
		additionalInfo.setStudiedEnglishCenter(studiedEnglishCenter);
		if (this.schoolGradeId != null)
			additionalInfo.setSchoolGrade(SchoolGradeService.INSTANCE.findById(this.schoolGradeId));
		student.setAdditionalInfo(additionalInfo);
		
		student = StudentService.INSTANCE.persist(student);
		this.id = student.getId();
		
		this.addLoggingDetail(BaseAction.ADD, "logging.student.add", student.getFullName());
		return SUCCESS;
	}
	
	public String info() {
		REQUEST().setAttribute("scholarships", ScholarshipService.INSTANCE.findAll());
		REQUEST().setAttribute("types", StudentTypeService.INSTANCE.findAll());
		REQUEST().setAttribute("programs", ProgramService.INSTANCE.findAllAreActive());
		REQUEST().setAttribute("consultants", UserService.INSTANCE.findAllByRole("Consulting"));
		REQUEST().setAttribute("student", StudentService.INSTANCE.findById(this.id));
		REQUEST().setAttribute("shifts", PlacementShiftService.INSTANCE.findAll());
		REQUEST().setAttribute("schoolGrades", SchoolGradeService.INSTANCE.findAll());
		REQUEST().setAttribute("schoolNames", SchoolNameService.INSTANCE.findAll());
		REQUEST().setAttribute("surveyInfos", SurveyInfoService.INSTANCE.findAllAreActive());
		return SUCCESS;
	}
	
	public String edit() {
		Student student = StudentService.INSTANCE.findById(this.id);
		student.setFirstName(this.firstName);
		student.setLastName(this.lastName);
		student.setNickName(this.nickName);
		student.setGender(Gender.create(this.gender));
		student.setDateOfBirth(DateUtil.fromString(this.dateOfBirth, this.i18n("format.date")));
		student.setAddress(this.address);
		student.setPhoneNumber(this.phoneNumber);
		student.setEmail(this.email);
		student.setType(StudentTypeService.INSTANCE.findById(this.typeId));
		student.setComment(this.comment);
		student.setScholarship(ScholarshipService.INSTANCE.findById(this.scholarshipId));
		student.setPlacementShift(PlacementShiftService.INSTANCE.findById(this.placementShiftId));
		student.setConsultedDate(DateUtil.fromString(this.consultedDate, this.i18n("format.date")));
		student.setProgram(ProgramService.INSTANCE.findById(this.programId));
		student.setTestDate(DateUtil.fromString(this.testDate, this.i18n("format.date")));
		student.setConsultant(UserService.INSTANCE.findById(this.consultantId));
		
		if(!Checker.isNull(this.surveyInfos)) {
			Set<SurveyInfo> infos = new HashSet<SurveyInfo>(); 
			for (Long each : this.surveyInfos) {
				SurveyInfo surveyInfo = SurveyInfoService.INSTANCE.findById(each);
				infos.add(surveyInfo);
			}
			student.setSurveys(infos);
		}
		
		/* halnt */
		AdditionalInfo additionalInfo = null;
		additionalInfo = student.getAdditionalInfo();
		if (additionalInfo == null) {
			additionalInfo = new AdditionalInfo();
		}
		additionalInfo.setSchool(this.school);
		additionalInfo.setClazz(this.clazz);
		additionalInfo.setStudiedEnglishCenter(studiedEnglishCenter);
		if (this.schoolGradeId != null)
			additionalInfo.setSchoolGrade(SchoolGradeService.INSTANCE.findById(this.schoolGradeId));
		student.setAdditionalInfo(additionalInfo);
		
		StudentService.INSTANCE.persist(student);
		
		this.addLoggingDetail(BaseAction.EDIT, "logging.student.edit", student.getFullName(), student.getId(), student.getDateOfBirth());
		return SUCCESS;
	}
	
	public String delete() {
		String fullName = StudentService.INSTANCE.findById(this.id).getFullName();
		if(StudentService.INSTANCE.deleteById(this.id)) {
			this.setKeyMessage("confirm.deleteSuccessfully", "student");
			this.addLoggingDetail(BaseAction.DELETE, "logging.student.delete", fullName);
		}
		else {
			this.setKeyMessage("confirm.deleteUnsuccessfully", "student");
		}
		return SUCCESS;
	}
	
	public String deleteSearch() {
		if(!Checker.isNull(this.ids)) {
			for (Long each : this.ids) {
				Student student = StudentService.INSTANCE.findById(each);
				String name = student.getFullName();
				if(StudentService.INSTANCE.deleteById(each)) {
					this.addLoggingDetail(BaseAction.DELETE, "logging.student.deleteSearch", name);
				}
			}
		}
		return SUCCESS;
	}
	
	public String merge() {
		if(!Checker.isNull(this.ids)) {
			StudentService.INSTANCE.merge(this.ids, this.ids[0]);
		}
		// TODO Logging
		return SUCCESS;
	}
	
	public String showCacheSearchResult() {
		Student student = (Student) SESSION().getAttribute("searchStudent");
		if(Checker.isNull(student))
			student = new Student();
		Collection<Student> students = StudentService.INSTANCE.find(student);
		REQUEST().setAttribute("students", students);
		return SUCCESS;
	}
	
	public String showSearch() {
		REQUEST().setAttribute("scholarships", ScholarshipService.INSTANCE.findAll());
		REQUEST().setAttribute("types", StudentTypeService.INSTANCE.findAll());
		REQUEST().setAttribute("programs", ProgramService.INSTANCE.findAll());
		REQUEST().setAttribute("shifts", PlacementShiftService.INSTANCE.findAll());
		REQUEST().setAttribute("consultants", UserService.INSTANCE.findAllByRole("Consulting"));
		REQUEST().setAttribute("schoolGrades", SchoolGradeService.INSTANCE.findAll());
		REQUEST().setAttribute("schoolNames", SchoolNameService.INSTANCE.findAll());
		return SUCCESS;
	}
	
	public String search() {
		Student student = new Student();
		student.setFirstName(this.firstName);
		student.setLastName(this.lastName);
		student.setNickName(this.nickName);
		student.setGender(Gender.create(this.gender));
		student.setDateOfBirth(DateUtil.fromString(this.dateOfBirth, this.i18n("format.date")));
		student.setAddress(this.address);
		student.setPhoneNumber(this.phoneNumber);
		student.setEmail(this.email);
		
		student.setScholarship(ScholarshipService.INSTANCE.findById(this.scholarshipId));
		
		if (!Checker.isEmpty(this.school) || !Checker.isEmpty(this.clazz) || !Checker.isEmpty(this.studiedEnglishCenter) || !Checker.isNull(this.schoolGradeId)) {
			AdditionalInfo additionalInfo = new AdditionalInfo();
			additionalInfo.setSchool(this.school.trim().equals("")?null:this.school);
			student.setAdditionalInfo(additionalInfo);
		}
		//additionalInfo.setClazz(this.clazz);
		//additionalInfo.setStudiedEnglishCenter(this.studiedEnglishCenter);
		//additionalInfo.setSchoolGrade(SchoolGradeServiceImpl.INSTANCE.findById(this.schoolGradeId));
		//student.setAdditionalInfo(AdditionalInfoServiceImpl.INSTANCE.findBySchool(this.school));
		
		student.setPlacementShift(PlacementShiftService.INSTANCE.findById(this.placementShiftId));
		student.setType(StudentTypeService.INSTANCE.findById(this.typeId));
		student.setComment(this.comment);
		student.setConsultedDate(DateUtil.fromString(this.consultedDate, this.i18n("format.date")));
		student.setFromConsultedDate(DateUtil.fromString(this.fromConsultedDate, this.i18n("format.date")));
		student.setToConsultedDate(DateUtil.fromString(this.toConsultedDate, this.i18n("format.date")));
		student.setProgram(ProgramService.INSTANCE.findById(this.programId));
		student.setTestDate(DateUtil.fromString(this.testDate, this.i18n("format.date")));
		student.setConsultant(UserService.INSTANCE.findById(this.consultantId));
		
		SESSION().setAttribute("searchStudent", student);
		
		this.addLoggingDetail(BaseAction.SEARCH, "logging.student.search");
		return SUCCESS;
	}
	
	public String showHistory() {
		Student student = StudentService.INSTANCE.findById(this.id);
		Level lastLevel = student.getLastLevel();
		Level nextLevel = LevelService.INSTANCE.findNextLevel(lastLevel);
		REQUEST().setAttribute("student", student);
		REQUEST().setAttribute("nextLevel", nextLevel);
		return SUCCESS;
	}
	
	public String requestByFirstNameAndLastName() {
		Student searchStudent = new Student();
		searchStudent.setFirstName(this.firstName);
		searchStudent.setLastName(this.lastName);
		Collection<Student> students = StudentService.INSTANCE.find(searchStudent);
		List<SelectOption> optionList = new ArrayList<SelectOption>();
		for(Student student : students) {
			SelectOption selectOption = new SelectOption();
			selectOption.setValue(String.valueOf(student.getId()));
			selectOption.setCaption("Name: " + student.getFirstName() + " " + student.getLastName() + ", Gender: " + this.i18n(student.getGender().getKey()) + ", DOB: " + DateUtil.toString(student.getDateOfBirth(), this.i18n("format.date")));
			optionList.add(selectOption);
		}
		try {
			RESPONSE().getWriter().write(new JSONSerializer().serialize(optionList));
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return NONE;
	}
	
	public String showAllRecently() {
		REQUEST().setAttribute("students", StudentService.INSTANCE.findAllRecently());
		return SUCCESS;
	}
	
	public String reportAllRecently() {
		Collection<Student> students = StudentService.INSTANCE.findAllRecentlyByDate();
		if (students.size() <= 0) return SUCCESS;
		HSSFWorkbook wb = ExcelUtil.getTemplate("student-recent-monthly.xls");
			
		Map<String, CellStyle> styles = createStyles(wb);
		if (Checker.isNull(wb)) return NONE;

		/* BEGIN WRITING DATA */
		HSSFSheet sheet = wb.getSheetAt(0);
		
		HSSFRow row = sheet.getRow(1);
		HSSFCell cell = row.getCell(0);
		cell.setCellValue("Tháng " + DateUtil.toString(DateUtil.newDate(), "MM/yyyy"));
		
		int initRow = 4;
		generateCells(initRow, students, sheet, styles, 0);
		
		sheet.createFreezePane(0, initRow);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\"" 
				+ "Recently students in month Report - " 
				+ DateUtil.toString(DateUtil.newDate(), "MM.yyyy") + ".xls\"");
		try {
			wb.write(RESPONSE().getOutputStream());
			this.addLoggingDetail(BaseAction.REPORT, "logging.student.report.recent.monthly", DateUtil.toString(DateUtil.newDate(), "MM/yyyy"));
		} catch (IOException e) {
			e.printStackTrace();
			return NONE;
		}
		return NONE;
	}
	
	/**
	 *  generate the cells for students list 
	 */
	private int generateCells(int initRow, Collection<Student> details, HSSFSheet sheet, Map<String, CellStyle> styles, int numberOfCols) {
		int count = 1;
		
		for (Student each : details) {
			HSSFRow row = sheet.createRow(initRow++);
			String datePattern = "MM/dd/yyyy";
			
			ExcelUtil.makeNumericCell(row, 0, count++, styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 1, DateUtil.toString(each.getConsultedDate(), datePattern), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 2, each.getLastName(), styles, "cell");
			ExcelUtil.makeStringCell(row, 3, each.getFirstName(), styles, "cell");
			ExcelUtil.makeStringCell(row, 4, (each.getGender().name() == "MALE" ? "Nam" : "Nữ"), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 5, DateUtil.toString(each.getDateOfBirth(), datePattern), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 6, each.getPhoneNumber(), styles, "cell");
			ExcelUtil.makeStringCell(row, 7, each.getProgram().getName(), styles, "cell");
			
			ExcelUtil.makeStringCell(row, 8, (each.getLastOffering() != null ? each.getLastOffering().getName() : ""), styles, "cell");
			ExcelUtil.makeStringCell(row, 9, DateUtil.toString(each.getTestDate(), datePattern), styles, "cellCenter");
			ExcelUtil.makeStringCell(row, 10, each.getComment(), styles, "cell");
			
			row.setHeightInPoints(25);
		}
		return initRow;
	}
	
	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getComment() {
		return comment;
	}

	public void setComment(String comment) {
		this.comment = comment;
	}

	public String getDateOfBirth() {
		return dateOfBirth;
	}

	public void setDateOfBirth(String dateOfBirth) {
		this.dateOfBirth = dateOfBirth;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getPhoneNumber() {
		return phoneNumber;
	}

	public void setPhoneNumber(String phoneNumber) {
		this.phoneNumber = phoneNumber;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Long[] getIds() {
		return ids;
	}

	public void setIds(Long[] ids) {
		this.ids = ids;
	}

	public Long getTypeId() {
		return typeId;
	}

	public void setTypeId(Long typeId) {
		this.typeId = typeId;
	}

	public String getConsultedDate() {
		return consultedDate;
	}

	public void setConsultedDate(String consultedDate) {
		this.consultedDate = consultedDate;
	}

	public Long getProgramId() {
		return programId;
	}

	public void setProgramId(Long programId) {
		this.programId = programId;
	}

	public String getTestDate() {
		return testDate;
	}

	public void setTestDate(String testDate) {
		this.testDate = testDate;
	}

	public Long getConsultantId() {
		return consultantId;
	}

	public void setConsultantId(Long consultantId) {
		this.consultantId = consultantId;
	}

	public String getFromConsultedDate() {
		return fromConsultedDate;
	}

	public void setFromConsultedDate(String fromConsultedDate) {
		this.fromConsultedDate = fromConsultedDate;
	}

	public String getToConsultedDate() {
		return toConsultedDate;
	}

	public void setToConsultedDate(String toConsultedDate) {
		this.toConsultedDate = toConsultedDate;
	}

	public Long getScholarshipId() {
		return scholarshipId;
	}

	public void setScholarshipId(Long scholarshipId) {
		this.scholarshipId = scholarshipId;
	}

	public Long getPlacementShiftId() {
		return placementShiftId;
	}

	public void setPlacementShiftId(Long placementShiftId) {
		this.placementShiftId = placementShiftId;
	}

	public String getNickName() {
		return nickName;
	}

	public void setNickName(String nickName) {
		this.nickName = nickName;
	}

	public String getSchool() {
		return school;
	}

	public void setSchool(String school) {
		this.school = school;
	}

	public String getClazz() {
		return clazz;
	}

	public void setClazz(String clazz) {
		this.clazz = clazz;
	}

	public Long getSchoolGradeId() {
		return schoolGradeId;
	}

	public void setSchoolGradeId(Long schoolGradeId) {
		this.schoolGradeId = schoolGradeId;
	}

	public String getStudiedEnglishCenter() {
		return studiedEnglishCenter;
	}

	public void setStudiedEnglishCenter(String studiedEnglishCenter) {
		this.studiedEnglishCenter = studiedEnglishCenter;
	}	
	
	public Long[] getSurveyInfos() {
		return surveyInfos;
	}

	public void setSurveyInfos(Long[] surveyInfos) {
		this.surveyInfos = surveyInfos;
	}
	
	/**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)13);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        
        Font subtitleFont = wb.createFont();
        subtitleFont.setFontHeightInPoints((short)12);
        subtitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subtitleFont);
        styles.put("subtitle", style);

        Font cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
        styles.put("cell", style);

        cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
        DataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellMoney", style);
        
        Font cCellFont = wb.createFont();
        cCellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cCellFont);
        styles.put("cellCenter", style);
        
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cCellFont);
        styles.put("cellRight", style);
        
        Font bold = wb.createFont();
        bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
        bold.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(bold);
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellRightBold", style);
        
        cCellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(cCellFont);
        styles.put("cellGreen", style);
        
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(bold);
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellRed", style);
        
        return styles;
    }
}